package com.spring.boot.service;

import java.sql.PreparedStatement;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import com.spring.boot.entity.Student;

@Service
public class StudentService implements BaseService {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public List<Student> getAllStudents() {
		String sql = "select id, name, age, mdate from student";
		return (List<Student>) jdbcTemplate.query(sql, (rs, rowNum) -> {
			Student stu = new Student();
			stu.setId(rs.getInt("id"));
			stu.setAge(rs.getInt("age"));
			stu.setName(rs.getString("name"));
			stu.setMdate(rs.getDate("mdate"));
			return stu;
		});
	}

	public Student getStudentById(int id) {
		// 此处为jdbc的问题,当为空时抛出异常
		String sql = "select id, name, age, mdate from student where id = " + id;

		return queryNullAble(jdbcTemplate, sql, (rs, rowNum) -> {
			Student stu = new Student();
			stu.setId(rs.getInt("id"));
			stu.setAge(rs.getInt("age"));
			stu.setName(rs.getString("name"));
			stu.setMdate(rs.getDate("mdate"));
			return stu;
		});
	}

	public void addStudent(String name, int age) {
		String sql = "insert into student (name, age, mdate) values ('" + name + "'," + age + ", now())";
		int res = jdbcTemplate.update(sql);
		// jdbcTemplate.execute(sql);
		System.out.println(res);
	}

	public void delStudent(int id) {
		String sql = "delete from student where id = " + id;
		int res = jdbcTemplate.update(sql);
		// jdbcTemplate.execute(sql);
		System.out.println(res);
	}

	public int addStudentAndReturn(String name, int age) {
		String sql = "insert into student (name, age, mdate) values ('" + name + "'," + age + ", now())";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		int autoIncId = 0;

		jdbcTemplate.update((con) -> {
			return con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
		} , keyHolder);

		autoIncId = keyHolder.getKey().intValue();

		return autoIncId;
	}

}
